package rubix.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class PartManager {

    private static final String TBLNAME = "part";

    private static void handleSQLException(SQLException ex, String sql, String... parameters) {
        String msg = "Unable to access data; SQL=" + sql + "\n";

        for (String parameter : parameters) {
            msg += "," + parameter;
        }

        Logger.getLogger(PoManager.class.getName()).log(Level.SEVERE, msg, ex);
        throw new RuntimeException(msg, ex);
    }
    //method
    public static void prepareTables(){
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";
        
        try {
            conn = ConnectionManager.getConnection();            
            
            sql = "DROP TABLE IF EXISTS part";
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
            
            sql = "CREATE TABLE IF NOT EXISTS part("
                    + "partid VARCHAR(50) NOT NULL,"
                    + "itemid VARCHAR(7) NOT NULL,"
                    + "partdescription VARCHAR(50) NOT NULL,"
                    + "partprice VARCHAR(10) NOT NULL,"                     					
                    + "partexpirydate VARCHAR(8),"
                    + "PRIMARY KEY (partid)"
                    + ")";
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "prepareTables");
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }    
    
    public static void addPart(Part part) {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";
        
        String partId = part.getPartId();
        String itemId = part.getItemId();
        String partDescription = part.getPartDescription();
        String partPrice = part.getPartPrice();
        String partExpiryDate = part.getPartExpiryDate();
        
        try {
            conn = ConnectionManager.getConnection();
            
            sql = "INSERT INTO " + TBLNAME + " (partid,itemid,partdescription,partprice,partexpirydate) VALUES (?,?,?,?,?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, partId);
            stmt.setString(2, itemId);
            stmt.setString(3, partDescription);
            stmt.setString(4, partPrice);
            stmt.setString(5, partExpiryDate);
            
            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "partid=" + partId + ",itemid=" + itemId + ",partdescription=" + partDescription 
                + ",partprice=" + partPrice + ",partexpirydate=" + partExpiryDate);
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }
    
    public static ArrayList<Part> retrieveAll() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "";
        ArrayList<Part> results = new ArrayList<Part>();

        try {
            conn = ConnectionManager.getConnection();

            sql = "SELECT partid,itemid,partdescription,partprice,partexpirydate FROM " + TBLNAME;
            stmt = conn.prepareStatement(sql);

            rs = stmt.executeQuery();
            while (rs.next()) {
                String partId = rs.getString("partid");
                String itemId = rs.getString("itemid");
                String partDescription = rs.getString("partdescription");
                String partPrice = rs.getString("partprice");
                String partExpiryDate = rs.getString("partexpirydate");

                results.add(new Part(partId,itemId,partDescription,partPrice,partExpiryDate));
            }
        } catch (SQLException ex) {
            handleSQLException(ex, sql);
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        return results;
    }

    public static Part retrievePart(String partId) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "";
        Part result = null;

        try {
            conn = ConnectionManager.getConnection();

            sql = "SELECT itemid,partdescription,partprice,partexpirydate FROM " + TBLNAME + " WHERE partid = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, partId);

            rs = stmt.executeQuery();

            while (rs.next()) {
                String itemId = rs.getString("itemid");
                String partDescription = rs.getString("partdescription");
                String partPrice = rs.getString("partprice");
                String partExpiryDate = rs.getString("partexpirydate");

                result = new Part(partId,itemId,partDescription,partPrice,partExpiryDate);
            }
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "partid=" + partId);
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        return result;
    }
    
    /*
    public static void updateOnOrderQuantity(String poId, int onOrderQuantity) {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";

        try {
            conn = ConnectionManager.getConnection();

            sql = "UPDATE " + TBLNAME + " set onorderquantity=? where poid = ?";
            stmt = conn.prepareStatement(sql);

            stmt.setInt(1, onOrderQuantity);
            stmt.setString(2, poId);

            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "poid=" + poId + ",onorderquantity=" + onOrderQuantity);
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }*/
}